// $codepro.audit.disable instanceFieldNamingConvention, booleanMethodNamingConvention, com.instantiations.assist.eclipse.analysis.unusedReturnValue, closeInFinally

/** Packages */
package edu.gatech.team404.dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import edu.gatech.team404.tool.Log;

/**
 * A Data Access Layer class that provides basic services to the database.
 * @author Michael Dandy
 * @version 1.0
 */
public class IDatabase {
	
	/** Class tag for logging purpose */
	private final String TAG = "IDatabase";
	
	/** Server address of the database */
	//private final String HOST = "jdbc:mysql://localhost/cs2340";
	//private final String HOST = "jdbc:oracle:thin:@serverName:portNumber:dbName";
	private final String HOST = "jdbc:mysql://db4free.net:3306/cs2340";
	
	/** Database name */
	private final String DB_NAME = "cs2340";
	
	/** Username used to login to database server */
	//private final String USERNAME = "root";
	private final String USERNAME = "dsage";
	
	/** Password used to login to database server */
	private final String PASSWORD = "passW0rd";
	
	/** Connection to the database */
	private Connection conn = null;
	
	/** SQL Statement that will be run on the database server */
	private Statement sql = null;
	
	/** The result from executing SQL Statement*/
	private ResultSet result = null;
	
	/**
	 * Establish connection to the database.
	 * @throws ClassNotFoundException JDBC is not available
	 * @throws SQLException Error in SQL Connection
	 * @throws InstantiationException JDBC library is not found
	 * @throws IllegalAccessException Illegal Access to JDBC
	 */
	protected void connect() throws ClassNotFoundException, SQLException, 
		InstantiationException, IllegalAccessException {
		try {
			// Load the JDBC driver
			// We don't need the return value. We just need to load the class.
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (HOST, USERNAME, PASSWORD);
            
			/*
			// Load the JDBC driver
			// We don't need the return value. We just need to load the class.
		    Class.forName ("oracle.jdbc.driver.OracleDriver");
		    */

		    // Create a connection to the database
		    //String serverName = "127.0.0.1";
		    //String portNumber = "1521";
		    //conn = DriverManager.getConnection(HOST, USERNAME, PASSWORD);
		}
		catch (SQLException e) {
			Log.error(TAG, e);
			throw e;
		}
		catch (ClassNotFoundException e) {
			Log.error(TAG, e);
			throw e;
		} catch (InstantiationException e) {
			Log.error(TAG, e);
			throw e;
		} catch (IllegalAccessException e) {
			Log.error(TAG, e);
			throw e;
		}
	}
	
	/**
	 * Close the connection to the database.
	 * @return true on success or false otherwise
	 */
	protected boolean close() {
		try {
			sql.close();
			result.close();
			conn.close();
			return true;
		}
		catch (SQLException e) {
			Log.error(TAG, e);
			return false;
		}
		catch (Exception e) {
			Log.error(TAG, e);
			return false;
		}
	}
	
	/**
	 * Create table to the database with the given table name.
	 * @param tableName the name of the table
	 * @param args the table definition
	 * @return true on success or false otherwise
	 */
	protected boolean createTable(String tableName, String args) {
		final String query = "CREATE TABLE " + tableName + " (" + args + ")";
		Log.info(TAG, "Create " + tableName + " (" + args + ")");
		return executeUpdate(query);
	}
	
	/**
	 * Remove the specified table from the database.
	 * @param tableName the name of the table
	 * @return true on success or false otherwise
	 */
	protected boolean dropTable(String tableName) {
		final String query = "DROP TABLE IF EXISTS " + tableName;
		Log.info(TAG, "Drop table " + tableName);
		return executeUpdate(query);
	}
	
	/**
	 * Check if specific table exists in the database.
	 * @param tableName the name of the table
	 * @return true on success or false otherwise
	 */
	protected boolean isTableExist(String tableName) {
		final String query = "SHOW TABLES";
		result = executeQuery(query);
		if (result != null) {
			try {
				while (result.next()) {
				     if (result.getString(1).equalsIgnoreCase(tableName)) {
				    	 return true;
				     }
				}
				return false;
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return false;
			}
		}
		return false;
	}
	
	/**
	 * Check if specific column exists in the table.
	 * @param tableName the name of the table
	 * @param columnName the name of the column
	 * @return true if the column exists in the database or false otherwise
	 */
	protected boolean isColumnExist(String tableName, String columnName) {
		final String query = "SHOW COLUMNS FROM " + tableName;
		result = executeQuery(query);
		if (result != null) {
			try {
				while (result.next()) {
				     if (result.getString(1).equalsIgnoreCase(columnName)) {
				    	 return true;
				     }
				}
				return false;
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return false;
			}
		}
		return false;
	}
	
	/**
	 * Insert a column into the database.
	 * @param tableName the name of the table
	 * @param columnName the name of the column
	 * @param args table definition and/or option
	 * @return true on success or false otherwise
	 */
	protected boolean insertColumn(String tableName, String columnName, String args) {
		final String query = "ALTER TABLE " + tableName + " ADD COLUMN " + columnName 
							 + " " + args;
		Log.info(TAG, "Insert column " + columnName + " to table " + tableName);
		return executeUpdate(query);
	}
	
	/**
	 * Remove the specified column from the table.
	 * @param tableName the name of the table
	 * @param columnName the name of the column
	 * @return true on success or false otherwise
	 */
	protected boolean dropColumn(String tableName, String columnName) {

		final String query = "ALTER TABLE " + tableName + " DROP " + columnName;
		Log.info(TAG, "Drop column " + columnName + " from table " + tableName);
		return executeUpdate(query);
	}
	
	/**
	 * Check whether there is a connection has been established or not.
	 * @return true if the connection has been established or false otherwise
	 */
	protected boolean isConnected() {
		try {
			if (conn == null || conn.isClosed()) {
				return false;
			}
			
			return true;
		} 
		catch (SQLException e) {
			Log.error(TAG, e);
			return false;
		}
	}

	/**
	 * Execute the SQL statement. Warning connection will not be closed after 
	 * the function call. 
	 * @param query the SQL statement
	 * @return the result
	 */
	protected ResultSet executeQuery(String query) {
		result = null;
		try {
			if (!isConnected()) {
				connect();
			}
			
			sql = conn.createStatement();
	        result = sql.executeQuery (query);
		} 
		catch (SQLException e) {
			Log.error(TAG, e);
		}
		catch (Exception e) {
			Log.error(TAG, e);
		}
		return result;
	}
	
	/**
	 * Executes the given SQL statement, which may be an INSERT, UPDATE, 
	 * or DELETE statement or an SQL statement that returns nothing, 
	 * such as an SQL DDL statement.  
	 * @param query the SQL statement
	 * @return the result
	 */
	protected boolean executeUpdate(String query) {
		int row = 0;
		try {
			if (!isConnected()) {
				connect();
			}
			
			sql = conn.createStatement();
	        row = sql.executeUpdate(query);     
		} 
		catch (SQLException e) {
			Log.error(TAG, e);
			return false;
		}
		catch (Exception e) {
			Log.error(TAG, e);
			return false;
		}
		finally {
			close();
		}
		return row != 0 ? true : false;
	}
	
	/**
	 * Clear the specified table.
	 * @param tableName the name of the table.
	 * @return
	 */
	public boolean clearTable (String tableName) {
		final String query = "TRUNCATE TABLE " + tableName;
		return executeUpdate(query);
	}
	
	/**
	 * Reset the database. It will empty all the table exists in the database.
	 * @return true on success or false otherwise
	 */
	public boolean resetDatabase() {
		boolean ret = true;
		Statement statement = null;
		String query = "SELECT table_name FROM information_schema.tables "
						+ "WHERE table_schema = \'" + DB_NAME + "\'";
		
		final ResultSet tableNames = executeQuery(query);
		if (tableNames != null) {
			try {
				if (!isConnected()) {
					connect();
				}

				statement = conn.createStatement();
		        
				while (tableNames.next()) {
				     query = "TRUNCATE TABLE " + tableNames.getString(1);
				     statement.executeUpdate(query);
				}
				
				tableNames.close();
				statement.close();
				close();
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				ret = false;
			}
			catch (Exception e) {
				Log.error(TAG, e);
				ret = false;
			}
		}
		
		return ret;
	}
}
